<?php
/**
 * A build script for the db
 *
 *
 **/
require 'db/db.php';

$db = openDbConn();

try {
  $db->beginTransaction();
  $db->exec( 'DROP TABLE IF EXISTS post;' );
  $db->exec( 'DROP TABLE IF EXISTS thread;' );
  $db->exec( 'DROP TABLE IF EXISTS forum;' );
  $db->exec( 'DROP TABLE IF EXISTS permGroup;' ); 
  $db->exec( 'DROP TABLE IF EXISTS user;' );

  $db->exec( 'CREATE TABLE post 
                (id INTEGER PRIMARY KEY, 
                 title Text(200) NOT NULL, 
                 datum CLOB NOT NULL, 
                 rank Integer NOT NULL,
                 parent Integer NOT NULL, 
                 date Integer NOT NULL, 
                 tid Integer NOT NULL, 
                 uid Integer NOT NULL);' );
  $db->exec( 'CREATE TABLE thread 
                (id INTEGER PRIMARY KEY, 
                 fid Integer NOT NULL,
                 mode Text(200));' );
  $db->exec( 'CREATE TABLE forum 
                (id INTEGER PRIMARY KEY, 
                 title Text(200) NOT NULL, 
                 datum Text(200) NOT NULL, 
                 rank Integer NOT NULL);' );
  $db->exec( 'CREATE TABLE permGroup 
                (id INTEGER PRIMARY KEY,
                 name Text(250) NOT NULL, 
                 perm CLOB );' );
  $db->exec( 'CREATE TABLE user 
                (id INTEGER PRIMARY KEY, 
                 name Text(200) UNIQUE NOT NULL, 
                 pass Text(250) NOT NULL, 
                 mail Text(200) UNIQUE NOT NULL,
                 joinDate Integer NOT NULL,
                 permGroup Integer);');

// I'm beggining to rethink my permGroup in favor of a simplier Int-based system
  $db->exec( 'INSERT INTO permGroup (name, perm) VALUES ("root","*")' );
  $db->exec( 'INSERT INTO permGroup (name, perm) VALUES ("moderator","view,new,edit.post,edit.thread,delete.post,delete.thread")' );
  $db->exec( 'INSERT INTO permGroup (name, perm) VALUES ("user","view,new")' );
  $db->exec( 'INSERT INTO permGroup (name, perm) VALUES ("anonymous","view")' );
  $db->exec( 'INSERT INTO user (id, name, pass, mail, joinDate, permGroup) VALUES (0, "guest", "", "guest@no-reply.com", '.time().', 4);' );
  $db->exec( 'INSERT INTO user (name, pass, mail, joinDate, permGroup) VALUES ("root", "99adc231b045331e514a516b4b7680f588e3823213abe901738bc3ad67b2f6fcb3c64efb93d18002588d3ccc1a49efbae1ce20cb43df36b38651f11fa75678e8", "root@no-reply.com", '.time().', 1);' );
  
  $db->exec( 'INSERT INTO forum (title, datum, rank) VALUES("f1","lots of love",1)');
  $db->exec( 'INSERT INTO thread (fid, mode) VALUES((SELECT MAX(id) FROM forum),"none")' );
  $db->exec( 'INSERT INTO post (title, datum, rank, date, tid, uid, parent) 
                   VALUES( "a1t", "a1b", 1, 1182617486, (SELECT MAX(id) FROM thread), 0, 0 )' );
  $db->exec( 'INSERT INTO post (title, datum, rank, date, tid, uid, parent) 
                   VALUES( "a2t", "a2b", 2, 1182617487, (SELECT MAX(id) FROM thread), 0, 0 )' );
  $db->exec( 'INSERT INTO thread (fid, mode) VALUES(1,"none")' );
  $db->exec( 'INSERT INTO post (title, datum, rank, date, tid, uid, parent) 
                   VALUES( "b1t", "b1b", 1, 1182617486, (SELECT MAX(id) FROM thread), 0, 0 )' );
  $db->exec( 'INSERT INTO post (title, datum, rank, date, tid, uid, parent) 
                   VALUES( "b2t", "b2b", 2, 1182617486, (SELECT MAX(id) FROM thread), 0, 0 )' );

  $db->exec( 'INSERT INTO forum (title, datum, rank) VALUES("f2","lots of love",1)');
  $db->exec( 'INSERT INTO thread (fid, mode) VALUES((SELECT MAX(id) FROM forum),"none")' );
  $db->exec( 'INSERT INTO post (title, datum, rank, date, tid, uid, parent) 
                   VALUES( "A1t", "A1b", 1, 1182617486, (SELECT MAX(id) FROM thread), 0, 0 )' );

  $db->commit();
} catch ( Exception $e ) {
  $db->rollBack();
  die( $e->getMessage() );
}

echo 'DB BUILT';

?>
